import pymysql
from pymysql.cursors import DictCursor
from datetime import datetime
import json
from flask import Flask, request, jsonify

app = Flask(__name__)

# 数据库配置
DB_CONFIG = {
    'host': 'localhost',
    'user': 'root',
    'password': 'yourpassword',
    'database': 'bank_system',
    'charset': 'utf8mb4',
    'cursorclass': DictCursor
}

def get_db_connection():
    return pymysql.connect(**DB_CONFIG)

@app.route('/api/customer/add', methods=['POST'])
def add_customer():
    data = request.json
    required_fields = ['customerName', 'PID', 'telephone']
    if not all(field in data for field in required_fields):
        return jsonify({'success': False, 'message': '缺少必要字段'}), 400
    
    try:
        conn = get_db_connection()
        with conn.cursor() as cursor:
            sql = """
            INSERT INTO userInfo (customerName, PID, telephone, address)
            VALUES (%s, %s, %s, %s)
            """
            cursor.execute(sql, (data['customerName'], data['PID'], data['telephone'], data.get('address', '')))
            conn.commit()
            return jsonify({'success': True, 'customerID': cursor.lastrowid})
    except pymysql.err.IntegrityError as e:
        return jsonify({'success': False, 'message': '身份证号已存在'}), 400
    except Exception as e:
        return jsonify({'success': False, 'message': str(e)}), 500
    finally:
        conn.close()

@app.route('/api/customer/update/<int:customer_id>', methods=['POST'])
def update_customer(customer_id):
    data = request.json
    try:
        conn = get_db_connection()
        with conn.cursor() as cursor:
            sql = """
            UPDATE userInfo 
            SET customerName=%s, telephone=%s, address=%s 
            WHERE customerID=%s
            """
            cursor.execute(sql, (data['customerName'], data['telephone'], data.get('address', ''), customer_id))
            conn.commit()
            return jsonify({'success': True})
    except Exception as e:
        return jsonify({'success': False, 'message': str(e)}), 500
    finally:
        conn.close()

@app.route('/api/customer/delete/<int:customer_id>', methods=['POST'])
def delete_customer(customer_id):
    try:
        conn = get_db_connection()
        with conn.cursor() as cursor:
            # 检查是否有关联的银行卡
            cursor.execute("SELECT COUNT(*) AS count FROM cardInfo WHERE customerID=%s", (customer_id,))
            result = cursor.fetchone()
            if result['count'] > 0:
                return jsonify({'success': False, 'message': '该客户有关联的银行卡，无法删除'}), 400
            
            cursor.execute("DELETE FROM userInfo WHERE customerID=%s", (customer_id,))
            conn.commit()
            return jsonify({'success': True})
    except Exception as e:
        return jsonify({'success': False, 'message': str(e)}), 500
    finally:
        conn.close()

@app.route('/api/customer/list', methods=['GET'])
def list_customers():
    try:
        conn = get_db_connection()
        with conn.cursor() as cursor:
            cursor.execute("SELECT * FROM userInfo")
            customers = cursor.fetchall()
            return jsonify({'success': True, 'data': customers})
    except Exception as e:
        return jsonify({'success': False, 'message': str(e)}), 500
    finally:
        conn.close()

@app.route('/api/card/list', methods=['GET'])
def list_cards():
    try:
        conn = get_db_connection()
        with conn.cursor() as cursor:
            cursor.execute("""
            SELECT c.*, u.customerName, d.savingName 
            FROM cardInfo c 
            JOIN userInfo u ON c.customerID = u.customerID
            JOIN deposit d ON c.savingID = d.savingID
            """)
            cards = cursor.fetchall()
            return jsonify({'success': True, 'data': cards})
    except Exception as e:
        return jsonify({'success': False, 'message': str(e)}), 500
    finally:
        conn.close()

@app.route('/api/card/update/<string:card_id>', methods=['POST'])
def update_card(card_id):
    data = request.json
    try:
        conn = get_db_connection()
        with conn.cursor() as cursor:
            sql = """
            UPDATE cardInfo 
            SET savingID=%s, IsReportLoss=%s 
            WHERE cardID=%s
            """
            cursor.execute(sql, (data['savingID'], data['IsReportLoss'], card_id))
            conn.commit()
            return jsonify({'success': True})
    except Exception as e:
        return jsonify({'success': False, 'message': str(e)}), 500
    finally:
        conn.close()

@app.route('/api/card/change_password/<string:card_id>', methods=['POST'])
def change_password(card_id):
    data = request.json
    if 'old_password' not in data or 'new_password' not in data:
        return jsonify({'success': False, 'message': '缺少密码字段'}), 400
    
    try:
        conn = get_db_connection()
        with conn.cursor() as cursor:
            # 验证旧密码
            cursor.execute("SELECT pass FROM cardInfo WHERE cardID=%s", (card_id,))
            result = cursor.fetchone()
            if not result or result['pass'] != data['old_password']:
                return jsonify({'success': False, 'message': '旧密码不正确'}), 400
            
            # 更新密码
            cursor.execute("UPDATE cardInfo SET pass=%s WHERE cardID=%s", (data['new_password'], card_id))
            conn.commit()
            return jsonify({'success': True})
    except Exception as e:
        return jsonify({'success': False, 'message': str(e)}), 500
    finally:
        conn.close()

@app.route('/api/transaction/deposit', methods=['POST'])
def deposit():
    data = request.json
    required_fields = ['cardID', 'amount']
    if not all(field in data for field in required_fields):
        return jsonify({'success': False, 'message': '缺少必要字段'}), 400
    
    try:
        conn = get_db_connection()
        with conn.cursor() as cursor:
            # 存款
            sql = "UPDATE cardInfo SET balance=balance+%s WHERE cardID=%s"
            cursor.execute(sql, (data['amount'], data['cardID']))
            
            # 触发器会自动记录交易
            conn.commit()
            return jsonify({'success': True})
    except Exception as e:
        return jsonify({'success': False, 'message': str(e)}), 500
    finally:
        conn.close()

@app.route('/api/transaction/withdraw', methods=['POST'])
def withdraw():
    data = request.json
    required_fields = ['cardID', 'amount', 'password']
    if not all(field in data for field in required_fields):
        return jsonify({'success': False, 'message': '缺少必要字段'}), 400
    
    try:
        conn = get_db_connection()
        with conn.cursor() as cursor:
            # 验证密码和余额
            cursor.execute("SELECT pass, balance FROM cardInfo WHERE cardID=%s", (data['cardID'],))
            result = cursor.fetchone()
            if not result:
                return jsonify({'success': False, 'message': '银行卡不存在'}), 400
            if result['pass'] != data['password']:
                return jsonify({'success': False, 'message': '密码不正确'}), 400
            if result['balance'] < data['amount']:
                return jsonify({'success': False, 'message': '余额不足'}), 400
            
            # 取款
            sql = "UPDATE cardInfo SET balance=balance-%s WHERE cardID=%s"
            cursor.execute(sql, (data['amount'], data['cardID']))
            
            # 触发器会自动记录交易
            conn.commit()
            return jsonify({'success': True})
    except Exception as e:
        return jsonify({'success': False, 'message': str(e)}), 500
    finally:
        conn.close()

@app.route('/api/transaction/transfer', methods=['POST'])
def transfer():
    data = request.json
    required_fields = ['from_card', 'to_card', 'amount', 'password', 'remark']
    if not all(field in data for field in required_fields):
        return jsonify({'success': False, 'message': '缺少必要字段'}), 400
    
    try:
        conn = get_db_connection()
        with conn.cursor() as cursor:
            # 验证密码
            cursor.execute("SELECT pass FROM cardInfo WHERE cardID=%s", (data['from_card'],))
            result = cursor.fetchone()
            if not result or result['pass'] != data['password']:
                return jsonify({'success': False, 'message': '密码不正确'}), 400
            
            # 调用存储过程执行转账
            cursor.callproc('sp_transfer', 
                          (data['from_card'], data['to_card'], data['amount'], data['remark'], 0))
            result = cursor.fetchone()
            
            if result['result'] == 1:
                conn.commit()
                return jsonify({'success': True})
            elif result['result'] == -1:
                return jsonify({'success': False, 'message': '余额不足'}), 400
            elif result['result'] == -2:
                return jsonify({'success': False, 'message': '转入卡不存在'}), 400
            else:
                return jsonify({'success': False, 'message': '转账失败'}), 400
    except Exception as e:
        return jsonify({'success': False, 'message': str(e)}), 500
    finally:
        conn.close()

@app.route('/api/transaction/history/<string:card_id>', methods=['GET'])
def transaction_history(card_id):
    try:
        conn = get_db_connection()
        with conn.cursor() as cursor:
            cursor.execute("""
            SELECT tradeDate, tradeType, tradeMoney, remark 
            FROM tradeInfo 
            WHERE cardID=%s 
            ORDER BY tradeDate DESC
            """, (card_id,))
            transactions = cursor.fetchall()
            return jsonify({'success': True, 'data': transactions})
    except Exception as e:
        return jsonify({'success': False, 'message': str(e)}), 500
    finally:
        conn.close()

@app.route('/api/report/customer_csv', methods=['GET'])
def export_customer_csv():
    try:
        conn = get_db_connection()
        with conn.cursor() as cursor:
            cursor.execute("SELECT * FROM userInfo")
            customers = cursor.fetchall()
            
            # 生成CSV内容
            csv_content = "客户编号,客户姓名,身份证号,联系电话,联系地址\n"
            for customer in customers:
                csv_content += f"{customer['customerID']},{customer['customerName']},{customer['PID']}," \
                             f"{customer['telephone']},{customer['address']}\n"
            
            return jsonify({'success': True, 'data': csv_content})
    except Exception as e:
        return jsonify({'success': False, 'message': str(e)}), 500
    finally:
        conn.close()

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5555, debug=True)
